package com.cch.platform.file.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cch.platform.util.StringUtil;

public class ExcelReader {

	public final static String EXCEL2003_SUFFIX = ".xls";
	public final static String EXCEL2010_SUFFIX = ".xlsx";
	public final static SimpleDateFormat df=new SimpleDateFormat("yyyyMMdd");
	private final static DecimalFormat doublef = new DecimalFormat("0000.0000");
	
	private int sheetIndex=0;
	private int startCol=0;
	private int endCol=-1;
	private int nameRow=0;
	private int typeRow=1;
	private int startRow;
	private int endRow=-1;
	private String excelVersion=EXCEL2010_SUFFIX;
	private int colNum;
	private int rowNum;
	
	private Sheet sheet;
	private String[] names;
	private String[] codes;
	private ExcelCellType[] types;
	private ExcelNullType[] nullable;
	private Object[] defaults;
	
	List<Map<String,Object>> tableData;
			
	public ExcelReader() {
		
	}

	public List<Map<String,Object>> readExcel(InputStream source ){
		tableData=new ArrayList<Map<String,Object>>();
		initProperties(source);
		readTitle();
		readData();
		return tableData;
	}
	
	private void readData() {
		for(int i=0;i<rowNum;i++){
			Map<String, Object> rowMap=new HashMap<String, Object>();
			Row row=sheet.getRow(i+startRow);
			for (int j = 0; j < colNum; j++) {
				Cell cell=row.getCell(j + startCol);
				String value=null;
				if (cell != null) {
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						value = doublef.format(cell.getNumericCellValue());
						if (value.endsWith(".0000")) {
							value = value.substring(0, value.length() - 5);
						}
					} else {
						value = cell.getStringCellValue();
					}
				}

				Object ob=str2Obj(value, types[j]);
				if(ob==null&&nullable[j]==ExcelNullType.nullable){
					ob=defaults[j];
				}else if(ob==null&&nullable[j]!=ExcelNullType.nullable){
					throw new RuntimeException((i+rowNum+1)+" row:"+names[j]+" can not be null");
				}
				rowMap.put(codes[j], ob);
			}
			tableData.add(rowMap);
		}
	}

	private void readTitle() {
		names = new String[colNum];
		codes = new String[colNum];
		types = new ExcelCellType[colNum];
		nullable = new ExcelNullType[colNum];
		defaults = new Object[colNum];

		if (nameRow < 0) {
			for (int i = 0; i < colNum; i++)
				names[i] = "COL" + (i + startCol);
		} else {
			Row row = sheet.getRow(nameRow);
			for (int i = 0; i < colNum; i++) {
				names[i] = row.getCell(i + startCol).getStringCellValue();
			}
		}
		if (typeRow < 0) {
			for (int i = 0; i < colNum; i++) {
				codes[i] = "COL" + (i + startCol);
				types[i] = ExcelCellType.String;
				nullable[i] = ExcelNullType.nullable;
				defaults[i] = null;
			}
		} else {
			Row row = sheet.getRow(typeRow);
			for (int i = 0; i < colNum; i++) {
				String typestr=null;
				Cell cell=row.getCell(i + startCol);
				if(cell!=null){
					typestr=cell.getStringCellValue();
				}
				String[] values =StringUtil.split2Array(typestr,"/");
				if(values.length>0){
					codes[i] = values[0];
				}else{
					codes[i] = "COL" + (i + startCol);;
				}
				if(values.length>1){
					types[i] = ExcelCellType.toEnum(values[1]);
				}else{
					types[i] = ExcelCellType.String;
				}
				if (values.length > 2) {
					nullable[i] = ExcelNullType.toEnum(values[2]);
				} else {
					nullable[i] = ExcelNullType.nullable;
				}
				if (values.length > 3) {
					defaults[i] = str2Obj(values[3], types[i]);
				} else {
					defaults[i] = null;
				}
			}
		}
	}

	private void initProperties(InputStream source) {
		Workbook workbook = null;
		try {
			if (excelVersion.equals(EXCEL2003_SUFFIX)) {
				workbook = new HSSFWorkbook(source);
			} else {
				workbook = new XSSFWorkbook(source);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = workbook.getSheetAt(sheetIndex);
		if (endRow < 0) {
			endRow = sheet.getPhysicalNumberOfRows();
		}
		if (endCol < 0) {
			endCol = sheet.getRow(0).getPhysicalNumberOfCells();
		}
		if (startRow <= typeRow) {
			startRow = typeRow + 1;
		}
		if (startRow <= nameRow) {
			startRow = nameRow + 1;
		}

		colNum = endCol - startCol;
		rowNum=endRow-startRow;
	}

	public enum ExcelCellType {
		String, Interger, Double,Boolean, Date, DateTime;

		public static ExcelCellType toEnum(String str) {
			if ("String".equalsIgnoreCase(str))
				return ExcelCellType.String;
			if ("Interger".equalsIgnoreCase(str))
				return ExcelCellType.Interger;
			if ("Double".equalsIgnoreCase(str))
				return ExcelCellType.Double;
			if ("Boolean".equalsIgnoreCase(str))
				return ExcelCellType.Boolean;
			if ("Date".equalsIgnoreCase(str))
				return ExcelCellType.Date;
			if ("DateTime".equalsIgnoreCase(str))
				return ExcelCellType.DateTime;
			throw new RuntimeException("type: " + str + " is not support");
		}
	}

	public enum ExcelNullType {
		notnull, unique, nullable;
		
		public static ExcelNullType toEnum(String str) {
			if ("notnull".equalsIgnoreCase(str))
				return ExcelNullType.notnull;
			if ("unique".equalsIgnoreCase(str))
				return ExcelNullType.unique;
			if ("nullable".equalsIgnoreCase(str))
				return ExcelNullType.nullable;
			throw new RuntimeException("nulltype: " + str + " is not support");
		}
	}
	
	private Object str2Obj(String str,ExcelCellType type){
		Object re=null;
		if(StringUtil.isEmpty(str)){
			return re;
		}
		
		switch (type) {
		case String:
			re=str;
			break;
		case Interger:
			re=Integer.parseInt(str);
			break;
		case Double:
			re=Double.parseDouble(str);
			break;
		case Date:
			try {
				re=df.parse(str);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			break;
		case DateTime:
			break;
		case Boolean:
			re=Boolean.parseBoolean(str);
			break;
		default:
			break;
		}
		
		return re;
	}
	public int getSheetIndex() {
		return sheetIndex;
	}

	public void setSheetIndex(int sheetIndex) {
		this.sheetIndex = sheetIndex;
	}

	public int getStartCol() {
		return startCol;
	}

	public void setStartCol(int startCol) {
		this.startCol = startCol;
	}

	public int getEndCol() {
		return endCol;
	}

	public void setEndCol(int endCol) {
		this.endCol = endCol;
	}

	public int getNameRow() {
		return nameRow;
	}

	public void setNameRow(int colNameRow) {
		this.nameRow = colNameRow;
	}

	public int getTypeRow() {
		return typeRow;
	}

	public void setTypeRow(int colTypeRow) {
		this.typeRow = colTypeRow;
	}

	public int getStartRow() {
		return startRow;
	}

	public void setStartRow(int startRow) {
		this.startRow = startRow;
	}

	public int getEndRow() {
		return endRow;
	}

	public void setEndRow(int endRow) {
		this.endRow = endRow;
	}

	public String getExcelVersion() {
		return excelVersion;
	}

	public void setExcelVersion(String excelVersion) {
		this.excelVersion = excelVersion;
	}
}
